#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive

${HIVE_HOME} -S -e "
create database if not exists  oe_dm;
drop table if exists oe_dm.dm_customer_relationship_detail;
create table oe_dm.dm_customer_relationship_detail(
        a_year string  comment   '年维度',
        a_month string  comment   '月维度',
        a_day string  comment   '日维度',
        area  string  comment '区域',
        subject_id  int  comment '学科id',
        subject_name  string  comment '学科名称',
        school_id  int  comment '校区id',
        school_name  string  comment '校区名称',
        origin_channel  string  comment '来源渠道',
        dp_id  int  comment  '部门id',
        name  string  comment '部门名称',
        line   string comment  '线上线下维度',
        CLUE   string comment  ' 新老线索学员',
        time_type    string comment  '日期维度类型',
        group_type string comment  '维度类型',
        count_total bigint  comment  '意向用户个数'
)COMMENT '意向主题宽表'
row format delimited fields terminated by '\t'
stored as TextFile
tblproperties ('TextFile.compress' = 'SNAPPY');

insert into oe_dm.dm_customer_relationship_detail
 with temp as (select
        substring (a_day,1,4) as a_year,
        substring (a_day,1,7) as a_month,
        a_day,
        area,
        subject_id,
        subject_name,
        school_id,
        school_name,
        origin_channel,
        dp_id,
        name,
        line,
        clue,
        group_type,
        count_cr

from oe_dws.dws_customer_relationship_detail)

 select
        a_year,
        a_month,
        a_day,
        area,
        subject_id,
        subject_name,
        school_id,
        school_name,
        origin_channel,
        dp_id,
        name,
        line,
        clue,
        case
             when grouping(a_year,a_month,a_day)=0 then '日'
             when grouping(a_year,a_month)=0 then '月'
             when grouping(a_year)=0 then '年'
             end as time_type,
        CASE WHEN GROUPING(area) = 0 THEN 'area'
             WHEN GROUPING(subject_id) = 0 AND GROUPING(subject_name) = 0 THEN 'subject'
             WHEN GROUPING(school_id) = 0 AND GROUPING(school_name) = 0 THEN 'school'
             WHEN GROUPING(origin_channel) = 0 THEN 'channel'
             WHEN GROUPING(dp_id) = 0 AND GROUPING(name) = 0 THEN 'dp'
             END AS group_type,
        sum(count_cr)  as  count_total
from temp
group by
grouping sets (
(a_year,a_month,a_day,line,clue),
(a_year,a_month,a_day,line,clue,area),
(a_year,a_month,a_day,line,clue,subject_id,subject_name),
(a_year,a_month,a_day,line,clue,school_id,school_name),
(a_year,a_month,a_day,line,clue,origin_channel),
(a_year,a_month,a_day,line,clue,dp_id,name),
(a_year,a_month,line,group_type,clue),
(a_year,a_month,line,group_type,clue,area),
(a_year,a_month,line,group_type,clue,subject_id,subject_name),
(a_year,a_month,line,group_type,clue,school_id,school_name),
(a_year,a_month,line,group_type,clue,origin_channel),
(a_year,a_month,line,group_type,clue,dp_id,name),
(a_year,line,group_type,clue),
(a_year,line,group_type,clue,area),
(a_year,line,group_type,clue,subject_id,subject_name),
(a_year,line,group_type,clue,school_id,school_name),
(a_year,line,group_type,clue,origin_channel),
(a_year,line,group_type,clue,dp_id,name)
);




"